package cn.gson.zuche.model.dao;


import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import cn.gson.zuche.model.GlobalParamHandler;
import cn.gson.zuche.model.bean.Orderxiaofeijilu;
import cn.gson.zuche.model.bean.User;
import cn.gson.zuche.model.jdbc.MySqlDb;
import cn.gson.zuche.model.jdbc.ParamHandler;
import cn.gson.zuche.model.jdbc.ResultHandler;

public class UserDao implements ResultHandler<User>{
	private MySqlDb db = MySqlDb.getInstance();
	/**
	 * 更新用户积分
	 */
	
	public boolean updateuserjf(String count,String usertel) throws SQLException{
		String sql="UPDATE user SET user_jf='"+count+"' WHERE user_tel='"+usertel+"'";
		return db.executeUpdate(sql)>0;
	}
	/**
	 * 获取用户积分
	 */
	public	List<User> selectuserjf(String usertel) throws SQLException{
		String sql="select user_jf from user where user_tel='"+usertel+"'";
		return db.executeQuery(sql, this);
	}
	/**
	 * 进行余额更新
	 */

	public boolean updateusermoney(String money,String usertel) throws SQLException{
		String sql="UPDATE user SET user_money='"+money+"' WHERE user_tel='"+usertel+"'";
		return db.executeUpdate(sql)>0;
	}
	
	/**
	 * 获取用户余额
	 */
	public	List<User> selectusermoney(String usertel) throws SQLException{
		String sql="select user_money from user where user_tel='"+usertel+"'";
		return db.executeQuery(sql, this);
	}
	
	/**
	 * 后台会员管理
	 */
	public List<User> select() throws SQLException{	
		String sql = "SELECT user_id,user_name,user_type_name as user_type,user_tel,user_email,user_zt_name as user_zt,user_money,user_jf from user_zt,user_type,`user` WHERE user.user_zt=user_zt.user_zt_id and user_type.user_type_id=user.user_type";
		return db.executeQuery(sql, this);
	}
	/**
	 * 修改所用的数据
	 */
	public List<User> xiugaiuser(String id) throws SQLException{	
		String sql = "SELECT user_id,pass_word,user_name,user_type_name as user_type,user_tel,user_email,user_zt_name as user_zt,user_money,user_jf from user_zt,user_type,`user` WHERE user.user_zt=user_zt.user_zt_id and user_type.user_type_id=user.user_type and user.user_id="+id+"";
		return db.executeQuery(sql, this);
	}
	/**
	 * 修改用户信息
	 * @throws SQLException 
	 */
	public boolean updateuser(final User u,String id) throws SQLException{
		String sql="UPDATE `user` SET pass_word=?,user_name=?,user_type=?,user_tel=?,user_email=?,user_zt=?,user_money=?,user_jf=? WHERE user_id="+id+"";
		return db.executeUpdate(sql, new GlobalParamHandler(u.getPassword(),u.getUserName(),u.getUserType(),u.getUserTel(),u.getUserEmail(),u.getUserZt(),u.getUserMoney(),u.getUserJf()))>0;
	}
	/**
	 * 根据用户电话号码查询用户
	 */
	public List<User> selectbytel(String tel) throws SQLException{	
		String sql = "SELECT user_name,user_type_name as user_type,user_tel,user_email,user_zt_name as user_zt,user_money,user_jf from user_zt,user_type,user WHERE  user.user_zt=user_zt.user_zt_id and user_type.user_type_id=user.user_type and (user_tel like'%"+tel+"%' or  user_type=(SELECT user_type_id from user_type where user_type_name like '%"+tel+"%'))";
		return db.executeQuery(sql, this);
	}
	/**
	 * 根据id查金钱
	 */
	public List<User> selectmoney(String tel) throws SQLException{
		String sql = "select user_money from user WHERE user_tel="+tel+"";
		return db.executeQuery(sql, this);
	}
	/**
	 * 插入新用户
	 */
	public boolean insertxinyonhu(final User user) throws SQLException{
		String sql = "INSERT into user(user_name,pass_word,user_email,user_tel,user_type,user_zt,user_money,user_jf) VALUES(?,?,?,?,?,?,?,?)";
		return db.executeUpdate(sql,new save(user))>0;
}
	/**
	 * 完整用户信息
	 * 
	 *
	 */
	public boolean updateuserxinxi(final User user,String name) throws SQLException{
		String sql = "UPDATE `user` SET user_email=?,user_sfz=?,user_zjlx=? WHERE user_name='"+name+"'";
		return db.executeUpdate(sql,new GlobalParamHandler(user.getUserEmail(),user.getUserSfz(),user.getUserZjlx()))>0;
}
	/**
	 * 根据用户id查询信息
	 * @author 
	 *
	 */
	public List<User> selectxinxi(Object userId) throws SQLException{
		String sql = "select * from user WHERE user_id="+userId+"";
		return db.executeQuery(sql, this);
	}
	
	
	
	
	
	private class save implements ParamHandler{
		User user;
		save(User user){
			this.user=user;
		}
		public void doHander(PreparedStatement pStatement) throws SQLException {
			pStatement.setString(1, user.getUserName());
			pStatement.setString(2, user.getPassword());
			pStatement.setString(3, user.getUserEmail());		
			pStatement.setString(4,	user.getUserTel());	
			pStatement.setObject(5,	user.getUserType());
			pStatement.setObject(6,	user.getUserZt());
			pStatement.setDouble(7,	user.getUserMoney());
			pStatement.setInt(8,user.getUserJf());
			pStatement.setString(9, user.getPasswdmd5());
		}
	}
	/**
	 * 充值
	 */
	public boolean updatemoney(String id,String money) throws SQLException{
			String sql = "UPDATE `user` SET user_money="+money+" WHERE user_tel='"+id+"'";
			return db.executeUpdate(sql)>0;
	}
	/**
	 * 根据用户id查找数据
	 * 用于->用户中心
	 * @throws SQLException 
	 */
	public List<User> findAllById(Long userId) throws SQLException{
		//String sql= "select * from user where user_id = "+id+"";
		String sql = "select user_id,user_name,pass_word,user_email,user_sex,user_sfz,user_zjlx,user_tel,user_jz,user_type_name as user_type ,user_zt_name as user_zt,user_money,user_yhq,user_xfnum,user_jf from user,user_zt,user_type WHERE user_id="+userId+" AND `user`.user_zt=user_zt.user_zt_id and `user`.user_type=user_type.user_type_id";
		return db.executeQuery(sql, this);
	}	
	
	/**
	 * 根据用户id修改用户信息:姓名、证件号码、电话号码、email		
	 * 用于用户中心
	 * @throws SQLException 
	 */
	public boolean updatemyinfor(User user) throws SQLException{
		String sql = "UPDATE user SET user_name=?,user_zjlx=?,user_sfz=?,user_tel=?,user_email=? WHERE user_id=?";
		return db.executeUpdate(sql, new GlobalParamHandler(user.getUserName(),user.getUserZjlx(),user.getUserSfz(),user.getUserTel(),user.getUserEmail(),user.getUserId()))>0;
	}
	/**
	 * 根据用户id修改用户密码
	 * @throws SQLException 
	 */

	public boolean updatepassword(String newpassword , Long userid,String passmd5) throws SQLException{
		String sql = "UPDATE user SET pass_word=?,passmd5=? WHERE user_id=?";
		return db.executeUpdate(sql, new GlobalParamHandler(newpassword,passmd5,userid))>0;
	}
	/**
	 * 根据用户id 修改余额  支付页面使用余额支付方式时调用 
	 * @param userid
	 * @param newmoney
	 * @return
	 * @throws SQLException
	 */
	public boolean updatemoneybyid(Long userid,Double newmoney) throws SQLException{
		String sql = "update user set user_money = "+newmoney+" where user_id = "+userid+"";
		return db.executeUpdate(sql)>0;
	}
	/**
	 * 用户中心根据积分来判断用户等级
	 * @param userId
	 * @param i
	 * @throws SQLException 
	 */
	public void upusertype(Long userId, int i) throws SQLException {
		String sql = "update user set user_type = "+i+" where user_id = "+userId+"";
		db.executeUpdate(sql);
	}
	/**
	 * 用户中心 修改优惠券数量 
	 * @param userid
	 * @param yhqnum
	 * @throws SQLException
	 */
	public void upyhqnum(Long userid,Integer yhqnum) throws SQLException {
		String sql = "update user set user_yhq='"+yhqnum+"' where user_id = '"+userid+"'";
		db.executeUpdate(sql);
	}
	@Override
	public User doHander(Map<String, Object> row) {
		User u = new User();

		if(row.get("user_id")!=null){
			u.setUserId(Long.parseLong(row.get("user_id")+""));
		}	
		u.setUserName((String)row.get("user_name"));
		u.setPassword((String) row.get("pass_word"));
		u.setUserEmail((String) row.get("user_email"));
		u.setUserSex((String) row.get("user_sex"));
		u.setUserZjlx((String) row.get("user_zjlx"));
		u.setUserSfz((String) row.get("user_sfz"));
		u.setUserTel((String) row.get("user_tel"));
		u.setUserJz((Integer) row.get("user_jz"));
		u.setUserType(row.get("user_type"));
		u.setUserZt(row.get("user_zt"));
		u.setUserMoney((Double) row.get("user_money"));
		u.setUserYhq((Integer) row.get("user_yhq"));
		u.setUserXfnum((Integer) row.get("user_xfnum"));
		u.setUserJf((Integer) row.get("user_jf"));
		u.setPasswdmd5(row.get("passmd5")+"");
		return u;
	}
	/*登陆界面通过输入tel跟密码进行判定此是否能找到*/
	public List<User> findAllByPasswd(String tel,String passmd5) throws SQLException{
		//String sql= "select * from user where user_id = "+id+"";
		String sql = "select * from user WHERE user_tel="+tel+" AND pass_word='"+passmd5+"'";
		return db.executeQuery(sql, this);
	}	
	public List<User> findAllByPasswdmd5(String tel,String passmd5) throws SQLException{
		//String sql= "select * from user where user_id = "+id+"";
		String sql = "select * from user WHERE user_tel="+tel+" AND passmd5='"+passmd5+"'";
		return db.executeQuery(sql, this);
	}	
	/*注册界面通过输入tel进行判定此是否能找到*/
	public List<User> findAllByTel(String tel) throws SQLException{
		//String sql= "select * from user where user_id = "+id+"";
		String sql = "select * from user WHERE user_tel="+tel+"";
		return db.executeQuery(sql, this);
	}
	/*注册用户信息至数据库*/
//	INSERT into user(user_name,pass_word,user_email,user_tel,user_type,user_zt,user_money,user_jf) VALUES(?,?,?,?,?,?,?,?)
	public boolean addUser(String userName,String userTel,String password,String passmd5) throws SQLException{
		String sql = "INSERT into user(user_name,user_tel,pass_word,passmd5) values(?,?,?,?)";
		return db.executeUpdate(sql, new GlobalParamHandler(userName,userTel,password,passmd5))>0;
	}
	
	/**
	 * 注册用户分配一个新优惠卷
	 */
	public boolean addUseryhq(Long userid) throws SQLException{
		String sql = "INSERT into user_yhq_use(user_id,yhq_id,yhq_number) values(?,1,1)";
		return db.executeUpdate(sql, new GlobalParamHandler(userid))>0;
	}
	/**
	 * 根据用户电话号码修改用户密码
	 * @throws SQLException 
	 */

	public boolean newpassword(String newpassword ,String passmd5,String tel) throws SQLException{
		String sql = "UPDATE user SET pass_word=?,passmd5=? WHERE user_tel=?";
		return db.executeUpdate(sql, new GlobalParamHandler(newpassword,passmd5,tel))>0;
	}
	
	private class insert implements ParamHandler{
		User user;
		insert(User user){
			this.user=user;
		}
		public void doHander(PreparedStatement pStatement) throws SQLException {
			pStatement.setString(1, user.getUserName());
			
			pStatement.setString(2, user.getUserEmail());		
			pStatement.setString(3,	user.getUserSfz());	
			pStatement.setObject(4,	user.getUserZjlx());
			
		}
	}
	
}
